import openpyxl
import pymysql
import re


def selectEcu(vehicle_model_id, ecu_name):
    # SQL 查询语句
    sql = "SELECT * FROM tab_ecu WHERE vehicle_model_id='" + \
        vehicle_model_id + "' AND ecu_name LIKE '%%%%%s%%%%'"
    sql = sql % (ecu_name)
    # 执行SQL语句
    cursor.execute(sql, ())
    # 获取一条记录列表
    return cursor.fetchone()


def addDtc(item):
    into = "insert into `tab_dtc` (`dtc_id`, `ecu_id`, `dtc_code`, `message_EN`, `message_CN`) values(%s,%s,%s,%s,%s)"
    values = (item['dtc_id'], item['ecu_id'], item['dtc_code'],
              item['message_EN'], item['message_CN'])
    cursor.execute(into, values)
    db.commit()


# 建立临时输出文件
f = open("out.txt", "w")  # 打开文件以便写入
# 建立mysql链接
db = pymysql.connect(host='gz-cdb-o43ukrr9.sql.tencentcdb.com',
                     port=60652,
                     user='dskj',
                     passwd='Dskj!2019',
                     db='bss-gq',
                     charset='utf8')
# db = pymysql.connect(host='10.2.3.5',
#                      port=3306,
#                      user='root',
#                      passwd='sirun@2018',
#                      db='bss-gq',
#                      charset='utf8')
# db = pymysql.connect(host='10.123.2.10',
#                      port=3306,
#                      user='sirun',
#                      passwd='sirun@2019',
#                      db='bss-gq',
#                      charset='utf8')
cursor = db.cursor()  # 使用cursor()方法获取操作游标
# 打开excel文件,获取工作簿对象
wb = openpyxl.load_workbook(
    "D:\\code\\python\\x9e_bss\\能力集脚本\\excel\\AM8_DTC_20200722.xlsx")

# 获取workbook中所有的表格
sheets = wb.sheetnames
# 车系
vehicle_ser = "7"
# 车型
#vehicle_model_id = "35"
vehicle_model_ids = {"5790", "5791", "5792",
                     "5794", "5795", "5796", "5797", "5798", "5799"}
number = 1
item = {}

print(sheets, file=f)
for vehicle_model_id in vehicle_model_ids:
    map = {}

    for sheet in sheets:
        if re.search('[A-Za-z]', sheet):
            # if sheet == "A26故障码库":
            ws = wb[sheet]
            if sheet.find("(") != -1:
                sheet = sheet[0:sheet.find("(")]
            if sheet.find("（") != -1:
                sheet = sheet[0:sheet.find("（")]
            # 查询是否有Ecu
            ecu = selectEcu(vehicle_model_id, sheet)
            # 查询不到放弃存储
            if ecu is None:
                print("未匹配的" + sheet, file=f)
                continue
            dtcList = []
            # 查询是否是重复
            if sheet in map:
                dtcList = map[sheet]
                for i in range(3, ws.max_row + 1):
                    item['dtc_id'] = vehicle_ser + \
                        vehicle_model_id + "%04d" % number
                    item['ecu_id'] = ecu[0]
                    dtcCode = ws.cell(row=i, column=2).value
                    # 故障码为空放弃
                    if dtcCode is None:
                        continue
                    dtcCode = str(dtcCode)
                    dtcCode = dtcCode.strip().replace("\n", "")
                    if dtcCode.find("(") != -1:
                        dtcCode = dtcCode[0:dtcCode.find("(")]
                    if dtcCode.find("（") != -1:
                        dtcCode = dtcCode[0:dtcCode.find("（")]
                    if dtcCode.find("删除") != -1:
                        continue
                    # 已存在放弃
                    if dtcCode in dtcList:
                        continue
                    item['dtc_code'] = dtcCode
                    item['message_EN'] = ws.cell(row=i, column=3).value
                    item['message_CN'] = ws.cell(row=i, column=4).value
                    print(item, file=f)
                    number = number + 1
                    dtcList.append(dtcCode)
                    addDtc(item)
                map[sheet] = dtcList
            else:
                for i in range(3, ws.max_row + 1):
                    item['dtc_id'] = vehicle_ser + \
                        vehicle_model_id + "%04d" % number
                    item['ecu_id'] = ecu[0]
                    dtcCode = ws.cell(row=i, column=2).value
                    # 故障码为空放弃
                    if dtcCode is None:
                        continue
                    dtcCode = str(dtcCode)
                    dtcCode = dtcCode.strip().replace("\n", "")
                    if dtcCode.find("(") != -1:
                        dtcCode = dtcCode[0:dtcCode.find("(")]
                    if dtcCode.find("（") != -1:
                        dtcCode = dtcCode[0:dtcCode.find("（")]
                    if dtcCode.find("删除") != -1:
                        continue
                    item['dtc_code'] = dtcCode
                    item['message_EN'] = ws.cell(row=i, column=3).value
                    item['message_CN'] = ws.cell(row=i, column=4).value
                    print(item, file=f)
                    number = number + 1
                    dtcList.append(dtcCode)
                    addDtc(item)
                map[sheet] = dtcList
